﻿using System;
using Eassets.Layered.Domain;
using System.Data.SqlClient;
using System.Data;
using System.Collections.ObjectModel;

namespace Eassets.Data
{
    public class EmployeeData
    {
        /// <summary>
        /// Inserts employee.
        /// </summary>
        /// <param name="employee"></param>
        public void AddEmployee(Employee employee)
        {
            SqlCommand command = new SqlCommand("SP_AddEmployee"); // Name of the SP
            CryptographyeAsset cryptographyeAsset = new CryptographyeAsset();
            employee.InitialPassword = cryptographyeAsset.Encrypt(employee.InitialPassword);
            employee.NewPassword = cryptographyeAsset.Encrypt(employee.NewPassword);
            employee.ConfirmPassword = cryptographyeAsset.Encrypt(employee.ConfirmPassword);
            CommonData commonData = new CommonData(); 
            commonData.ExecuteNonQuery(ConstructCommand(command, employee));
        }

        /// <summary>
        /// Updates employee.
        /// </summary>
        /// <param name="employee"></param>
        public void ModifyEmployee(Employee employee)
        {
            SqlCommand command = new SqlCommand("Sp_ModifyEmployee"); // Name of the SP
            command.Parameters.AddWithValue("@EmployeeId", employee.EmployeeId);
            CommonData commonData = new CommonData(); 
            commonData.ExecuteNonQuery(ConstructCommand(command, employee));
        }


        /// <summary>
        /// Delete employee.
        /// </summary>
        /// <param name="employeeId"></param>
        public void DeleteEmployee(long employeeId)
        {
            SqlCommand command = new SqlCommand("SP_DeleteEmployee"); // Name of the SP
            command.Parameters.AddWithValue("@EmployeeId", employeeId);
            command.CommandType = CommandType.StoredProcedure;
            CommonData commonData = new CommonData(); 
            commonData.ExecuteNonQuery(command);
        }

        /// <summary>
        /// GetbyId employee.
        /// </summary>
        /// <param name="employeeId"></param>
        /// <returns></returns>
        public Employee GetByIdEmployee(long employeeId)
        {
            SqlCommand command = new SqlCommand("Sp_GetEmployeeById");
            command.Parameters.AddWithValue("@EmployeeId", employeeId);
            command.CommandType = CommandType.StoredProcedure;
            CommonData commonData = new CommonData(); 
            SqlDataReader dataReader = commonData.ExecuteReader(command);
            Employee employee = new Employee();
            while (dataReader.Read())
            {
                if (!(String.IsNullOrEmpty(dataReader["EmployeeId"].ToString())))
                {
                    employee.EmployeeId = Convert.ToInt32(dataReader["EmployeeId"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["EmployeeName"].ToString())))
                {
                    employee.EmployeeName = Convert.ToString(dataReader["EmployeeName"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["Title"].ToString())))
                {
                    employee.Title = Convert.ToString(dataReader["Title"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["Initials"].ToString())))
                {
                    employee.Initials = Convert.ToString(dataReader["Initials"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["UserName"].ToString())))
                {
                    employee.UserName = Convert.ToString(dataReader["UserName"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["Email"].ToString())))
                {
                    employee.Email = Convert.ToString(dataReader["Email"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["PhoneNumber"].ToString())))
                {
                    employee.PhoneNumber = Convert.ToString(dataReader["PhoneNumber"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["Gender"].ToString())))
                {
                    employee.Gender = Convert.ToString(dataReader["Gender"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["DateOfBirth"].ToString())))
                {
                    employee.DateOfBirth = Convert.ToDateTime(dataReader["DateOfBirth"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["NICNo"].ToString())))
                {
                    employee.NICNo = Convert.ToString(dataReader["NICNo"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["EmployeeType"].ToString())))
                {
                    employee.EmployeeType = Convert.ToString(dataReader["EmployeeType"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["UserGroup"].ToString())))
                {
                    employee.UserGroup = Convert.ToString(dataReader["UserGroup"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["MaritalStatus"].ToString())))
                {
                    employee.MaritalStatus = Convert.ToString(dataReader["MaritalStatus"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["InitialPassword"].ToString())))
                {
                    employee.InitialPassword = Convert.ToString(dataReader["InitialPassword"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["NewPassword"].ToString())))
                {
                    employee.NewPassword = Convert.ToString(dataReader["NewPassword"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["ConfirmPassword"].ToString())))
                {
                    employee.ConfirmPassword = Convert.ToString(dataReader["ConfirmPassword"]);
                }
            }
            dataReader.Close();
            return employee;
        }

        /// <summary>
        /// GetAll employees.
        /// </summary>
        /// <returns></returns>
        public Collection<Employee> GetAllEmployee()
        {
            Collection<Employee> employees = new Collection<Employee>();
            SqlCommand command = new SqlCommand("SP_GetAllEmployee");
            command.CommandType = CommandType.StoredProcedure;
            CommonData commonData = new CommonData(); 
            SqlDataReader dataReader = commonData.ExecuteReader(command);
            Employee employee;
            while (dataReader.Read())
            {
                employee = new Employee();
                if (!(String.IsNullOrEmpty(dataReader["EmployeeId"].ToString())))
                {
                    employee.EmployeeId = Convert.ToInt32(dataReader["EmployeeId"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["EmployeeName"].ToString())))
                {
                    employee.EmployeeName = Convert.ToString(dataReader["EmployeeName"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["Title"].ToString())))
                {
                    employee.Title = Convert.ToString(dataReader["Title"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["Initials"].ToString())))
                {
                    employee.Initials = Convert.ToString(dataReader["Initials"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["UserName"].ToString())))
                {
                    employee.UserName = Convert.ToString(dataReader["UserName"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["Email"].ToString())))
                {
                    employee.Email = Convert.ToString(dataReader["Email"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["PhoneNumber"].ToString())))
                {
                    employee.PhoneNumber = Convert.ToString(dataReader["PhoneNumber"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["Gender"].ToString())))
                {
                    employee.Gender = Convert.ToString(dataReader["Gender"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["DateOfBirth"].ToString())))
                {
                    employee.DateOfBirth = Convert.ToDateTime(dataReader["DateOfBirth"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["NICNo"].ToString())))
                {
                    employee.NICNo = Convert.ToString(dataReader["NICNo"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["EmployeeType"].ToString())))
                {
                    employee.EmployeeType = Convert.ToString(dataReader["EmployeeType"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["UserGroup"].ToString())))
                {
                    employee.UserGroup = Convert.ToString(dataReader["UserGroup"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["MaritalStatus"].ToString())))
                {
                    employee.MaritalStatus = Convert.ToString(dataReader["MaritalStatus"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["InitialPassword"].ToString())))
                {
                    employee.InitialPassword = Convert.ToString(dataReader["InitialPassword"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["NewPassword"].ToString())))
                {
                    employee.NewPassword = Convert.ToString(dataReader["NewPassword"]);
                }
                if (!(String.IsNullOrEmpty(dataReader["ConfirmPassword"].ToString())))
                {
                    employee.ConfirmPassword = Convert.ToString(dataReader["ConfirmPassword"]);
                }

                employees.Add(employee);
            }
            dataReader.Close();
            return employees;
        }

        /// <summary>
        /// Get employee login details
        /// </summary>
        /// <param name="email"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public Employee GetEmployeeLoginDetails(string email, string password)
        {
            Employee employees = new Employee();

            SqlCommand command = new SqlCommand("SP_GetEmployeeLoginDetails");
            //command.CommandType = CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@Email", email);
            command.Parameters.AddWithValue("@NewPassword", password);
            command.CommandType = CommandType.StoredProcedure;
            CommonData commonData = new CommonData();
            SqlDataReader dataReader = commonData.ExecuteReader(command);
            Employee employee = new Employee();
            while (dataReader.Read())
            {
                try
                {
                    employee.InitialPassword = Convert.ToString(dataReader["InitialPassword"]);
                }
                catch (Exception)
                { }

                try
                {
                    employee.EmployeeId = Convert.ToInt64(dataReader["EmployeeId"]);
                }
                catch (Exception)
                { }
            }
            dataReader.Close();
            return employee;
        }

        public SqlCommand ConstructCommand(SqlCommand command, Employee employee)
        {
            command.CommandType = CommandType.StoredProcedure;

            try
            {
                command.Parameters.AddWithValue("@EmployeeName", employee.EmployeeName);
            }
            catch (Exception)
            { }
            try
            {
                command.Parameters.AddWithValue("@Title", employee.Title);
            }
            catch (Exception)
            { }
            try
            {
                command.Parameters.AddWithValue("@Initials", employee.Initials);
            }
            catch (Exception)
            { }
            try
            {
                command.Parameters.AddWithValue("@UserName", employee.UserName);
            }
            catch (Exception)
            { }
            try
            {
                command.Parameters.AddWithValue("@Email", employee.Email);
            }
            catch (Exception)
            { }
            try
            {
                command.Parameters.AddWithValue("@PhoneNumber", employee.PhoneNumber);
            }
            catch (Exception)
            { }
            try
            {
                command.Parameters.AddWithValue("@Gender", employee.Gender);
            }
            catch (Exception)
            { }
            try
            {
                command.Parameters.AddWithValue("@DateOfBirth", employee.DateOfBirth);
            }
            catch (Exception)
            { }
            try
            {
                command.Parameters.AddWithValue("@NICNo", employee.NICNo);
            }
            catch (Exception)
            { }
            try
            {
                command.Parameters.AddWithValue("@EmployeeType", employee.EmployeeType);
            }
            catch (Exception)
            { }
            try
            {
                command.Parameters.AddWithValue("@UserGroup", employee.UserGroup);
            }
            catch (Exception)
            { }
            try
            {
                command.Parameters.AddWithValue("@MaritalStatus", employee.MaritalStatus);
            }
            catch (Exception)
            { }
            try
            {
                command.Parameters.AddWithValue("@InitialPassword", employee.InitialPassword);
            }
            catch (Exception)
            { }
            try
            {
                command.Parameters.AddWithValue("@NewPassword", employee.NewPassword);
            }
            catch (Exception)
            { }
            try
            {
                command.Parameters.AddWithValue("@ConfirmPassword", employee.ConfirmPassword);
            }
            catch (Exception)
            { }
            return command;
        }

    }
}

